﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Collections;
using System.Data.SqlClient;
using cash_desk_model.src.model;

namespace cash_desk_model
{
    public class CashDeskService
    {
        private const String _connString = @"Data Source=COCO-PC\COCO; Initial Catalog=CASH_DESK_DB; Integrated Security=true";
        private static SqlConnection _dbConn = null;

        public static void SetupDBConn()
        {
            // Create database connection
            _dbConn = new SqlConnection();
            _dbConn.ConnectionString = _connString;
            _dbConn.Open();
        }

        public static void DestroyDBConn()
        {
            // Close databacse connection
            _dbConn.Close();
        }

        public Product GetProduct(String code)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetProduct";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@code", System.Data.SqlDbType.NVarChar).Value = code;
            SqlDataReader reader = command.ExecuteReader();

            // Get Product by code
            Product product = new Product();
            if (reader.Read())
            {
                product.ID = Int32.Parse(reader["ID"].ToString());
                product.ProductName = reader["NAME"].ToString();
                product.Code = reader["CODE"].ToString();
                product.Type = reader["TYPE"].ToString();
                product.Price = Double.Parse(reader["PRICE"].ToString()) / 100;
                product.Quantity = Double.Parse(reader["QUANTITY"].ToString());
                product.Description = reader["DESCRIPTION"].ToString();
                product.Provider = reader["PROVIDER"].ToString();
                reader.Close();
                return product;
            }
            reader.Close();

            return null;
        }

        public List<Product> GetProducts()
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetProducts";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataReader reader = command.ExecuteReader();

            // Get Products
            List<Product> products = new List<Product>();

            while (reader.Read())
            {
                // Get single product
                Product product = new Product();
                product.ID = Int32.Parse(reader["ID"].ToString());
                product.ProductName = reader["NAME"].ToString();
                product.Code = reader["CODE"].ToString();
                product.Type = reader["TYPE"].ToString();
                product.Price = Double.Parse(reader["PRICE"].ToString()) / 100;
                product.Quantity = Double.Parse(reader["QUANTITY"].ToString());
                product.Description = reader["DESCRIPTION"].ToString();
                product.Provider = reader["PROVIDER"].ToString();
          
                // Add product to products list
                products.Add(product);
            }
            reader.Close();

            return products;
        }

        public int SavePayment(DateTime payDate, String payType, Double amount, String cardId, String cashierName)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "SavePayment";
            command.Connection = _dbConn;

            // Save new payment
            command.Parameters.Add("@payDate", System.Data.SqlDbType.DateTime).Value = payDate;
            command.Parameters.Add("@payType", System.Data.SqlDbType.NVarChar, 50).Value = payType;
            command.Parameters.Add("@amount", System.Data.SqlDbType.Money).Value = amount;
            command.Parameters.Add("@cardId", System.Data.SqlDbType.NVarChar, 50).Value = cardId;
            command.Parameters.Add("@cashierName", System.Data.SqlDbType.NVarChar, 100).Value = cashierName;

            return Convert.ToInt32(command.ExecuteScalar());
        }

        public Product GetProductsByName(String name)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetProductsByName";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@name", System.Data.SqlDbType.NVarChar).Value = name;
            SqlDataReader reader = command.ExecuteReader();

            // Get Products by name
            Product product = new Product();
            if (reader.Read())
            {
                product.ID = Int32.Parse(reader["ID"].ToString());
                product.ProductName = reader["NAME"].ToString();
                product.Code = reader["CODE"].ToString();
                product.Type = reader["TYPE"].ToString();
                product.Price = Double.Parse(reader["PRICE"].ToString()) / 100;
                product.Quantity = Double.Parse(reader["QUANTITY"].ToString());
                product.Description = reader["DESCRIPTION"].ToString();
                product.Provider = reader["PROVIDER"].ToString();
                reader.Close();
                return product;
            }
            reader.Close();

            return null;
        }

        public Product GetProductsByType(String type)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetProductsByType";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@type", System.Data.SqlDbType.NVarChar).Value = type;
            SqlDataReader reader = command.ExecuteReader();

            // Get Products by type
            Product product = new Product();
            if (reader.Read())
            {
                product.ID = Int32.Parse(reader["ID"].ToString());
                product.ProductName = reader["NAME"].ToString();
                product.Code = reader["CODE"].ToString();
                product.Type = reader["TYPE"].ToString();
                product.Price = Double.Parse(reader["PRICE"].ToString()) / 100;
                product.Quantity = Double.Parse(reader["QUANTITY"].ToString());
                product.Description = reader["DESCRIPTION"].ToString();
                product.Provider = reader["PROVIDER"].ToString();
                reader.Close();
                return product;
            }
            reader.Close();

            return null;
        }

        public Product GetProductsByProvider(String provider)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetProductsByProvider";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@type", System.Data.SqlDbType.NVarChar).Value = provider;
            SqlDataReader reader = command.ExecuteReader();

            // Get Products by provider
            Product product = new Product();
            if (reader.Read())
            {
                product.ID = Int32.Parse(reader["ID"].ToString());
                product.ProductName = reader["NAME"].ToString();
                product.Code = reader["CODE"].ToString();
                product.Type = reader["TYPE"].ToString();
                product.Price = Double.Parse(reader["PRICE"].ToString()) / 100;
                product.Quantity = Double.Parse(reader["QUANTITY"].ToString());
                product.Description = reader["DESCRIPTION"].ToString();
                product.Provider = reader["PROVIDER"].ToString();
                reader.Close();
                return product;
            }
            reader.Close();

            return null;
        }

        public Double GetProductQuantity(String code)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Connection = _dbConn;

            // Get product quantity
            command.CommandText = "GetProductQuantity";
            command.Parameters.Add("@code", System.Data.SqlDbType.NVarChar, 100).Value = code;
            return Double.Parse(command.ExecuteScalar().ToString());
        }

        public int UpdateProductQuantity(String code, Double quantity)
        {
            // Get old product quantity
            Double oldQulantity = this.GetProductQuantity(code);

            // Get new quantity
            Double newQuantity = oldQulantity - quantity;

            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "UpdateProductQuantity";
            command.Connection = _dbConn;

            // Save new password
            command.Parameters.Add("@code", System.Data.SqlDbType.NVarChar, 100).Value = code;
            command.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal).Value = newQuantity;

            return command.ExecuteNonQuery();
        }

        public int SaveSoldProduct(long paymentId, String productCode, Double price, Double quantity)
        {
            // Get product by code
            Product soldProduct = this.GetProduct(productCode);

            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "SaveSoldProduct";
            command.Connection = _dbConn;

            // Save sold product
            command.Parameters.Add("@paymentId", System.Data.SqlDbType.Int).Value = paymentId;
            command.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 255).Value = soldProduct.ProductName;
            command.Parameters.Add("@code", System.Data.SqlDbType.NVarChar, 100).Value = productCode;
            command.Parameters.Add("@type", System.Data.SqlDbType.NVarChar, 100).Value = soldProduct.Type;
            command.Parameters.Add("@price", System.Data.SqlDbType.Money).Value = price;
            command.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal).Value = quantity;

            return command.ExecuteNonQuery();
        }

        public List<Product> GetProductsForSearch(string Name, string Code, string Type,string Description,string Provider)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetProductsForSearch";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@Code", Code));
            command.Parameters.Add(new SqlParameter("@Type", Type));
            command.Parameters.Add(new SqlParameter("@Description", Description));
            command.Parameters.Add(new SqlParameter("@Provider", Provider));

            SqlDataReader reader = command.ExecuteReader();

            List<Product> products = new List<Product>();

            while (reader.Read())
            {
                Product product = new Product();
                product.ID = Int32.Parse(reader["ID"].ToString());
                Name = reader["NAME"].ToString();
                product.ProductName = Name;
                Code = reader["CODE"].ToString();
                product.Code = Code;
                Type = reader["TYPE"].ToString();
                product.Type = Type;
                product.Price = Double.Parse(reader["PRICE"].ToString()) / 100;
                product.Quantity = Double.Parse(reader["QUANTITY"].ToString());
                product.Description = reader["DESCRIPTION"].ToString();
                product.Provider = reader["PROVIDER"].ToString();

                products.Add(product);
            }
            reader.Close();

            return products;
        }

        public int SaveNewProduct(String productName, String productType, Double price, 
            String description, double quantity, String provider)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "SaveNewProduct";
            command.Connection = _dbConn;

            // Save new product
            command.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 255).Value = productName;
            command.Parameters.Add("@type", System.Data.SqlDbType.NVarChar, 100).Value = productType;
            command.Parameters.Add("@price", System.Data.SqlDbType.Money).Value = price;
            command.Parameters.Add("@description", System.Data.SqlDbType.NVarChar, 255).Value = description;
            command.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal).Value = quantity;
            command.Parameters.Add("@provider", System.Data.SqlDbType.NVarChar, 255).Value = provider;

            byte inStock = 0;
            if (quantity > 0)
            {
                inStock = 1;
            }
            command.Parameters.Add("@inStock", System.Data.SqlDbType.Bit).Value = inStock;

            return command.ExecuteNonQuery();
        }

        public List<Payment> GetTransactionsForSearch(int transactionId, DateTime payDate,
            String cardId, String cashierName)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetTransactionsForSearch";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@transactionId", null));
            command.Parameters.Add(new SqlParameter("@payDate", null));
            command.Parameters.Add(new SqlParameter("@amount", null));
            command.Parameters.Add(new SqlParameter("@cardId", cardId));
            command.Parameters.Add(new SqlParameter("@cashierName", cashierName));

            SqlDataReader reader = command.ExecuteReader();

            List<Payment> transactions = new List<Payment>();

            while (reader.Read())
            {
                Payment transaction = new Payment();
                transaction.PaymentID = Int32.Parse(reader["ID"].ToString());
                transaction.PayDate = (DateTime)reader["PAY_DATE"];
                transaction.Amount = Double.Parse(reader["AMOUNT"].ToString());
                transaction.CardID = reader["CARD_ID"].ToString();
                transaction.CasheirName = reader["CASHIER_NAME"].ToString();

                transactions.Add(transaction);
            }
            reader.Close();

            return transactions;
        }

        public List<Product> GetSoldProducts(string paymentId)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetSoldProduct";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@paymentId", paymentId));

            SqlDataReader reader = command.ExecuteReader();

            List<Product> products = new List<Product>();

            while (reader.Read())
            {
                Product product = new Product();

                product.ProductName = reader["NAME"].ToString(); 
                product.Code = reader["CODE"].ToString();
                product.Type = reader["TYPE"].ToString();
                product.Price = Double.Parse(reader["PRICE"].ToString()) / 100;
                product.Quantity = Double.Parse(reader["QUANTITY"].ToString());

                products.Add(product);
            }
            reader.Close();

            return products;
        }
    }

}
